低版本Excel的多条件查询&拼接 |
您所在的位置:网站首页 › index match函数多条件查找 › 低版本Excel的多条件查询&拼接 |
Excel公式问题推荐社区:Excel函数与公式 - ExcelHome技术论坛 【问题】 根据A、B列信息,将C列汇总出拼接结果,即D列中的黄色标记结果。 “——这种问题方案只有五个,一是升级版本用textjoin,二是VBA(包括自定义函数),三是加辅助列标记分隔符再用phonetic或者用本例的方法,四是power query(2010版可以下载这个插件),五是手动。” 【限制】 1、Office低版本无法使用TEXTJOIN函数(2019版本支持); 2、EXCEL嵌入开发系统,不能使用VBA和插件; 3、需要自动统计结果,不能手动处理。 【解决】 1、辅助列 通过前台隐藏、文字改色的方式处理。 (1)辅助列函数 原解答函数=IF(AND(A2=A3,B2=B3),C2&","&D3,C2) 后自动化处理为=IF(AND(OFFSET($A$1,ROW()-1,,1,1)=OFFSET($A$1,ROW(),,1,1),OFFSET($B$1,ROW()-1,,1,1)=OFFSET($B$1,ROW(),,1,1)),OFFSET($C$1,ROW()-1,,1,1)&","&OFFSET($D$1,ROW(),,1,1),OFFSET($C$1,ROW()-1,,1,1)) (2)查询最终结果函数 原解答函数=INDEX($D:$D,MATCH(1,($A:$A=具体产品代码)*($B:$B=具体币种种类),)) 2、最终方案 思路:通过OFFSET函数匹配条件,获取B列C列方形区域,拼接;再将B列内容替换为逗号;最后将第一个逗号去除 举例:统计“1001”+“1-募集资金币种”。首先获取B2:C4区域,使用PHONETIC拼接内容后,再将“1-募集资金币种”替换为逗号,最后将第一个逗号去除。 =IF(代码单元格="","",SUBSTITUTE((SUBSTITUTE(PHONETIC(OFFSET($B$1,MATCH(代码单元格&"1-募集资金币种",$A:$A&$B:$B,0)-1,,LOOKUP(1,0/(($A:$A=代码单元格)*($B:$B="1-募集资金币种")),ROW($B:$B))-MATCH(代码单元格&"1-募集资金币种",$A:$A&$B:$B,0)+1,2)),"1-募集资金币种",",")),",","",1)) 【改进】 (1)用MAX函数替代LOOKUP函数,查找最后一行行号 LOOKUP(1,0/(($A:$A=代码单元格)*($B:$B="1-募集资金币种")),ROW($B:$B)) >>> MAX(($A:$A=代码单元格)*($B:$B="1-募集资金币种")*ROW($B:$B)) 注意:LOOKUP使用的是二分法,数据量高时前台会卡。INDEX(MATCH())函数效率高于LOOKUP函数。 (2)将“代码单元格”定位查找,使其可以自动查询。以“代码单元格”=B列为例: =OFFSET($B$1,ROW()-1,) 或者=ADDRESS(ROW(),2) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |